package com.xai.pg.service;

import com.xai.pg.data.DBField;
import com.xai.pg.data.DBInfo;
import com.xai.pg.data.DBTable;
import com.xai.pg.util.Conf;
import com.xai.pg.util.Connection;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author Kxvz
 */
@Service
public class DBInfoService {

    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String QUERY_TABLE_SQL_FMT = "select table_name,table_comment  from information_schema.tables  where table_schema = '%s';";
    private static final String QUERY_FILED_SQL_FMT = "select column_name,column_comment,data_type,column_default,is_nullable,character_octet_length,column_key from information_schema.columns where table_schema = '%s' and table_name='%s';";

    /**
     * 加载数据库相关信息
     *
     * @return
     */
    public DBInfo loadDBInfo() {
        Connection c = new Connection(DRIVER, Conf.getDBUrl(), Conf.getDBUser(), Conf.getDBPass());
        String tableSql = String.format(QUERY_TABLE_SQL_FMT, Conf.getDBName());
        List<Object[]> tableDatas = c.select(tableSql);

        DBInfo info = new DBInfo();
        for (Object[] table : tableDatas) {
            String tableName = String.valueOf(table[0]);
            String tableDesc = String.valueOf(table[1]);
            System.out.println("== tableName: " + tableName);

            DBTable t = new DBTable();
            t.setDesc(tableDesc);
            t.setTableName(tableName);

            String fieldSql = String.format(QUERY_FILED_SQL_FMT, Conf.getDBName(), tableName);
            List<Object[]> fieldDatas = c.select(fieldSql);
            for (Object[] fields : fieldDatas) {
                String fieldName = String.valueOf(fields[0]);
                System.out.println("---- fieldName: " + fieldName);
                String fieldDesc = String.valueOf(fields[1]);
                String fieldType = String.valueOf(fields[2]);
                String columnDefault = String.valueOf(fields[3]);
                String isNullable = String.valueOf(fields[4]);
                String characterLength = String.valueOf(fields[5]);
                String columnKey = String.valueOf(fields[6]);

                DBField f = new DBField();

                f.setType(fieldType);
                f.setName(fieldName);
                f.setDesc(fieldDesc);
                f.setColumnDefault("null".equals(columnDefault) ? null : ("".equals(columnDefault.trim()) || columnDefault.isEmpty()) ? null : columnDefault);
                f.setNullable("YES".equals(isNullable));
//                f.setColumnLength("null".equals(characterLength) ? null : Integer.valueOf(characterLength));
                f.setColumnLength(null);
                f.setPri("PRI".equals(columnKey));
                if (f.isPri()) {
                    t.setPk(f);
                }
                t.addFields(f);
            }
            info.addTables(t);
        }
        info.generatorTableSQL();
        return info;
    }
}
